package com.woniu.controller;

import com.woniu.pojo.Mark;
import com.woniu.pojo.PageBean;
import com.woniu.pojo.Stage;
import com.woniu.pojo.Student;
import com.woniu.service.StudentService;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.ss.usermodel.*;
import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpStatus;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.commons.io.FileUtils;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.text.DecimalFormat;
import java.util.*;

@Controller
@RequestMapping("student")
public class StudentController {
    @Resource
    private StudentService studentService;

    /**
     * 返回学生列表页面
     * @return
     */
    @RequestMapping("list")
    public String list(){
        return "student/list";
    }

    /**
     * 返回周考成绩列表页面
     * @return
     */
    @RequestMapping("weekscore")
    public String weekscore(){
        return "student/WeekScore";
    }

    /**
     * 返回阶段考成绩列表页面
     * @return
     */
    @RequestMapping("processscore")
    public String processscore(){
        return "student/processscore";
    }

    /**
     * 返回综合成绩列表页面
     * @return
     */
    @RequestMapping("synthesize")
    public String synthesize(){
        return "student/synthesize";
    }

    /**
     * 按照班级信息找班级未就业的人员
     * @return
     */
    @RequestMapping("sidDo")
    @ResponseBody
    public Map<String,Object> sidDo(Integer cid){
        Map<String,Object> resultMap=new HashMap<>();
        try {
            List<Student> studentList = studentService.getStudentsByCid(cid);
            resultMap.put("studentList", studentList);
            if(studentList.size()==0){
                resultMap.put("result", false);
            }else {
                resultMap.put("result", true);
            }
        }catch (Exception e){
            resultMap.put("result",false);
        }
        return resultMap;
    }


    /**
     * 学生列表分页展示
     * @param pageBean
     * @param student
     * @return
     */
    @RequestMapping("listDo")
    @ResponseBody
    public Map<String,Object> listDo(PageBean pageBean,Student student){
        Map<String,Object> resultMap=new HashMap<>();
        try {
            List<Student> studentList = studentService.getStudentsByPage(pageBean, student);
            resultMap.put("studentList", studentList);
            resultMap.put("pageBean", pageBean);
                resultMap.put("result", true);

        }catch (Exception e){
            resultMap.put("result",false);
        }
        return resultMap;
    }

    /**
     * 查询一个学生信息
     * @param sid
     * @return
     */
    @RequestMapping("edit")
    @ResponseBody
    public Map<String,Object> edit(Integer sid){
        Map<String,Object> resultMap=new HashMap<>();
        try {
            Student student = studentService.selectStudent(sid);
            resultMap.put("student",student);
            resultMap.put("result",true);
        }catch (Exception e){
            resultMap.put("result",false);
        }
        return resultMap;
    }

    /**
     * 修改学生信息
     * @param request
     * @param file
     * @param sid
     * @param stname
     * @param age
     * @param cid
     * @param sex
     * @param resource
     * @param uphone
     * @param sstate
     * @param qq
     * @param sos
     * @param sosphone
     * @param sfz
     * @return
     * @throws IOException
     */
    @RequestMapping("editDo1")
    @ResponseBody
    public boolean editDo1(HttpServletRequest request, @RequestParam("file1")MultipartFile file,Integer sid,String stname, Integer age, Integer cid, String sex, String resource, String uphone,String sstate,String qq,String sos,String sosphone,String sfz) throws IOException {
        String path= "D:\\Stuimg";//img子文件夹  文件保存的绝对路径  文件保存的绝对路径
        File parentFile=new File(path);
        if(!parentFile.exists()) {//不存在时进行创建目录
            parentFile.mkdirs();
        }
        String fileName = UUID.randomUUID()+ file.getOriginalFilename();
        if(!file.isEmpty()){
            byte [] bytes = file.getBytes();
            BufferedOutputStream bufferedOutputStream = new
                    BufferedOutputStream(new FileOutputStream(new File(parentFile,fileName)));
            bufferedOutputStream.write(bytes);
            bufferedOutputStream.flush();
            bufferedOutputStream.close();
        }
        fileName="../Stuimg/"+fileName;   //文件名
        Student student=new Student();    //新建对象并赋值
        student.setSid(sid);
        student.setStname(stname);
        student.setAge(age);
        student.setCid(cid);
        student.setSex(sex);
        student.setResource(resource);
        student.setUphone(uphone);
        student.setSstate(sstate);
        student.setQq(qq);
        student.setSos(sos);
        student.setSosphone(sosphone);
        student.setSfz(sfz);
        student.setIcon(fileName);
        System.out.println(fileName);
        System.out.println(student);
        studentService.updateStudent(student);   //调用方法
        return true;
    }


    /**
     * 增加学生
     * @param request
     * @param file
     * @param stname
     * @param age
     * @param cid
     * @param sex
     * @param resource
     * @return
     * @throws IOException
     */
    @RequestMapping("addDo")
    @ResponseBody
    public boolean addDo(HttpServletRequest request, @RequestParam("file")MultipartFile file, String stname, Integer age, Integer cid, String sex, String resource ) throws IOException {
        String path= "D:\\Stuimg";//img子文件夹
        File parentFile=new File(path);
        if(!parentFile.exists()) {//不存在时进行创建目录
            parentFile.mkdirs();
        }
        String fileName = UUID.randomUUID()+ file.getOriginalFilename();
        if(!file.isEmpty()){
            byte [] bytes = file.getBytes();
            BufferedOutputStream bufferedOutputStream = new
                    BufferedOutputStream(new FileOutputStream(new File(parentFile,fileName)));
            bufferedOutputStream.write(bytes);
            bufferedOutputStream.flush();
            bufferedOutputStream.close();
        }
        fileName="../Stuimg/"+fileName;
        Student student=new Student();
        student.setStname(stname);
        student.setAge(age);
        student.setCid(cid);
        student.setSex(sex);
        student.setResource(resource);
        student.setIcon(fileName);
        System.out.println(fileName);
        studentService.add(student);
        return true;
    }

    /**
     * 删除学生
     * @param sid
     * @return
     */
    @RequestMapping("delDo")
    @ResponseBody
    public Map<String,Object> delDo(Integer sid){
        Map<String,Object> resultMap=new HashMap<>();
        try {
            studentService.delStudent(sid);
            resultMap.put("result",true);
        }catch (Exception e){
            resultMap.put("result",false);
        }
        return resultMap;
    }

    /**
     * excel信息 导入到数据库 周考成绩
     * @param file
     * @param request
     * @param model
     * @return
     */
    @RequestMapping("importexcel")
    @ResponseBody
    public boolean uploadExcel(@RequestParam("file1")MultipartFile file, HttpServletRequest request, Model model) {
        //获取服务器端路径
        //  String path = "服务路经此处";
        //本地路径
        String path = "D:\\excel";
        //获取到上传文件名称
        String fileName =UUID.randomUUID()+ file.getOriginalFilename();
        System.out.println(fileName);
        //创建目标File
        File targetFile = new File(path + "\\" + fileName);
        //创建存储目录
        File targetPath = new File(path);
        System.out.println(targetPath);

        //判断服务器端目录是否存在,如果不存在创建目录
        if (!targetPath.exists()) {
            targetPath.mkdirs();
        }
        //把上传的文件存储到服务器端
        try {
            file.transferTo(targetFile);
        } catch (IllegalStateException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        //读取上传到服务器端的文件,遍历excel
        try {
            Workbook workbook = WorkbookFactory.create(targetFile);
            Sheet sheet = workbook.getSheet("Sheet1");
            //判断行数
            int rownum = sheet.getPhysicalNumberOfRows();
            for (int i = 1; i < rownum; i++) {    // 此处从1 开始，可以把标签头省略
                Row row = (Row) sheet.getRow(i);
                //判断单元格数量
                int cellnum = row.getPhysicalNumberOfCells();
                StringBuffer buf = new StringBuffer();
                for (int j = 0; j < cellnum; j++) {
                    Cell cell = row.getCell(j);
                    if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                        buf.append(cell.getStringCellValue() + "~");
                    } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
                        //创建数字格式化工具类
                        DecimalFormat df = new DecimalFormat("####");
                        //把从cell单元格读取到的数字,进行格式化防止科学计数法形式显示
                        buf.append(df.format(cell.getNumericCellValue()) + "~");
                    }
                }
                //单元格循环完成后读取到的是一行内容  1   aa  bb
                String hang = buf.toString();
                String[] rows = hang.split("~");
                Mark mark = new Mark();
                mark.setSid(Integer.valueOf(rows[1]));
                mark.setSno(Integer.valueOf(rows[2]));
                mark.setScore1(Double.valueOf(rows[3]));
                mark.setScore2(Double.valueOf(rows[4]));
                mark.setScore3(Double.valueOf(rows[5]));
                mark.setScore4(Double.valueOf(rows[6]));
                mark.setScore5(Double.valueOf(rows[7]));
                mark.setScore6(Double.valueOf(rows[8]));
                System.out.println("上传成绩信息:" + mark);
                Boolean f = studentService.insertMark(mark);
                if (!f){
                    System.out.println("-------------------------------");
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return true;
    }

    /**
     * 周考成绩列表展示
     * @param mark
     * @param student
     * @return
     */
    @RequestMapping("weeklistDo")
    @ResponseBody
    public Map<String,Object> weeklistDo(Mark mark,Student student){
        Map<String,Object> resultMap=new HashMap<>();
        try {
            List<Mark> markList = studentService.getStudents(mark,student);
            resultMap.put("markList", markList);
            if(markList.size()==0){
                resultMap.put("result", false);
            }else {
                resultMap.put("result", true);
            }
        }catch (Exception e){
            resultMap.put("result",false);
        }
        return resultMap;
    }

    /**
     * 查询一个周考成绩
     * @param mid
     * @return
     */
    @RequestMapping("editMark")
    @ResponseBody
    public Map<String,Object> editMark(Integer mid){
        Map<String,Object> resultMap=new HashMap<>();
        try {
            Mark mark = studentService.selectMark(mid);
            List<Stage> stageList = studentService.findAllStage();
            System.out.println(mark);
            resultMap.put("stageList", stageList);
            resultMap.put("mark",mark);
            resultMap.put("result",true);
        }catch (Exception e){
            resultMap.put("result",false);
        }
        return resultMap;
    }

    /**
     * 修改周考成绩
     * @param mark
     * @return
     */
    @RequestMapping("editMarkDo")
    @ResponseBody
    public Map<String,Object> editMarkDo(Mark mark){
        Map<String,Object> resultMap=new HashMap<>();
        try {
            studentService.updateMark(mark);
            System.out.println(mark);
            resultMap.put("result",true);
        }catch (Exception e){
            resultMap.put("result",false);
        }
        return resultMap;
    }

    /**
     * 查询所有阶段
     * @param
     * @return
     */
    @RequestMapping("stageDo")
    @ResponseBody
    public Map<String,Object> stageDo() {
        Map<String, Object> map = new HashMap<>();
        try {
            List<Stage> stageList = studentService.findAllStage();
            map.put("stageList", stageList);
            map.put("result", true);
        } catch (Exception e) {
            map.put("result", false);
        }
        return map;
    }

    /**
     * 阶段考成绩列表展示
     * @param mark
     * @param student
     * @return
     */
    @RequestMapping("processlistDo")
    @ResponseBody
    public Map<String,Object> processlistDo(Mark mark,Student student){
        Map<String,Object> resultMap=new HashMap<>();
        try {
            List<Mark> markList = studentService.getStudentsProcess(mark,student);
            resultMap.put("markList", markList);
            if(markList.size()==0){
                resultMap.put("result", false);
            }else {
                resultMap.put("result", true);
            }
        }catch (Exception e){
            resultMap.put("result",false);
        }
        return resultMap;
    }

    /**
     * excel信息 导入阶段考成绩到数据库
     * @param file
     * @param request
     * @param model
     * @return
     */
    @RequestMapping("importexcel1")
    @ResponseBody
    public boolean uploadExcel1(@RequestParam("file")MultipartFile file, HttpServletRequest request, Model model) {
        //获取服务器端路径
        //  String path = "服务路经此处";
        //本地路径
        String path = "D:\\excel1";
        //获取到上传文件名称
        String fileName =UUID.randomUUID()+ file.getOriginalFilename();
        System.out.println(fileName);
        //创建目标File
        File targetFile = new File(path + "\\" + fileName);
        //创建存储目录
        File targetPath = new File(path);
        System.out.println(targetPath);

        //判断服务器端目录是否存在,如果不存在创建目录
        if (!targetPath.exists()) {
            targetPath.mkdir();
        }
        //把上传的文件存储到服务器端
        try {
            file.transferTo(targetFile);
        } catch (IllegalStateException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        //读取上传到服务器端的文件,遍历excel
        try {
            Workbook workbook = WorkbookFactory.create(targetFile);
            Sheet sheet = workbook.getSheet("Sheet1");
            //判断行数
            int rownum = sheet.getPhysicalNumberOfRows();
            for (int i = 1; i < rownum; i++) {    // 此处从1 开始，可以把标签头省略
                Row row = (Row) sheet.getRow(i);
                //判断单元格数量
                int cellnum = row.getPhysicalNumberOfCells();
                StringBuffer buf = new StringBuffer();
                for (int j = 0; j < cellnum; j++) {
                    Cell cell = row.getCell(j);
                    if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                        buf.append(cell.getStringCellValue() + "~");
                    } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
                        //创建数字格式化工具类
                        DecimalFormat df = new DecimalFormat("####");
                        //把从cell单元格读取到的数字,进行格式化防止科学计数法形式显示
                        buf.append(df.format(cell.getNumericCellValue()) + "~");
                    }
                }
                //单元格循环完成后读取到的是一行内容  1   aa  bb
                String hang = buf.toString();
                String[] rows = hang.split("~");
                Mark mark = new Mark();
                mark.setSid(Integer.valueOf(rows[1]));
                mark.setSno(Integer.valueOf(rows[2]));
                mark.setScore6(Double.valueOf(rows[3]));
                mark.setAttendance(Double.valueOf(rows[4]));
                mark.setHomeworks(Double.valueOf(rows[5]));
                System.out.println("上传成绩信息:" + mark);
                Boolean f = studentService.insertMark(mark);
                if (!f){
                    System.out.println("-------------------------------");
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return true;
    }

    /**
     * 查询一个阶段考成绩
     * @param sid
     * @return
     */
    @RequestMapping("editMark1")
    @ResponseBody
    public Map<String,Object> editMark1(Integer sid){
        Map<String,Object> resultMap=new HashMap<>();
        try {
            Mark mark = studentService.selectMark1(sid);
            List<Stage> stageList = studentService.findAllStage();
            System.out.println(mark);
            resultMap.put("stageList", stageList);
            resultMap.put("mark",mark);
            resultMap.put("result",true);
        }catch (Exception e){
            resultMap.put("result",false);
        }
        return resultMap;
    }


    /**
     * 综合成绩列表展示
     * @param mark
     * @param student
     * @return
     */
    @RequestMapping("studengweeklistDo")
    @ResponseBody
    public Map<String,Object> studengweeklistDo(Mark mark,Student student){
        Map<String,Object> resultMap=new HashMap<>();
        try {
            List<Mark> markList = studentService.getStudents1(mark,student);
            resultMap.put("markList", markList);
            if(markList.size()==0){
                resultMap.put("result", false);
            }else {
                resultMap.put("result", true);
            }
        }catch (Exception e){
            resultMap.put("result",false);
        }
        return resultMap;
    }


    /**
     * 周考成绩信息 导出到excel
     * @param request
     * @param response
     * @param model
     * @return
     * @throws IOException
     */
    @RequestMapping(value = "/downloadexcel")
    public ResponseEntity<byte[]> down(HttpServletRequest request, HttpServletResponse response, Model model)
            throws IOException {
        // 从数据库读取数据
        List<Mark> allMark = studentService.findAllMarks();
        // 获取服务路径
        // String path = “服务路径”;
        //本地路径
        String path = "D:\\excel\\upload";
        File file1=new File(path);
        if(!file1.exists()){
            file1.mkdirs();
        }
        String filename = "bbb.xls";     // 也可修改为 xls
        // 存储File
        File tfile = new File(path + "\\" + filename);
        // 目录
        File mfile = new File(path);
        if (!tfile.exists()) {
            mfile.mkdir();
        }
        // 生成excel
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet("管理员信息表");
        int rownum = 0;
        XSSFRow row1 = sheet.createRow(rownum);
        row1.createCell(0).setCellValue("姓名");
        row1.createCell(1).setCellValue("学号");
        row1.createCell(2).setCellValue("班级");
        row1.createCell(3).setCellValue("第一周周考成绩");
        row1.createCell(4).setCellValue("第二周周考成绩");
        row1.createCell(5).setCellValue("第三周周考成绩");
        row1.createCell(6).setCellValue("第四周周考成绩");
        row1.createCell(7).setCellValue("第五周周考成绩");
        row1.createCell(8).setCellValue("第六周周考成绩");
        rownum =1;   //这里再次赋值 是为了把标题头加上
        for (Mark mark : allMark) {
            XSSFRow row = sheet.createRow(rownum);
            row.createCell(0).setCellValue(mark.getStudent().getStname());
            row.createCell(1).setCellValue(mark.getSid());
            row.createCell(2).setCellValue(mark.getStudent().getCid());
            row.createCell(3).setCellValue(mark.getScore1());
            row.createCell(4).setCellValue(mark.getScore2());
            row.createCell(5).setCellValue(mark.getScore3());
            row.createCell(6).setCellValue(mark.getScore4());
            row.createCell(7).setCellValue(mark.getScore5());
            row.createCell(8).setCellValue(mark.getScore6());
            rownum++;
        }
        // 保存workbook
        try {
            workbook.write(new FileOutputStream(tfile));
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        // 创建请求头对象
        HttpHeaders headers = new HttpHeaders();
        // 下载显示的文件名，解决中文名称乱码问题
        String downloadFileName = new String(filename.getBytes("UTF-8"), "iso-8859-1");
        // 通知浏览器以attachment(下载方式)打开
        headers.setContentDispositionFormData("attachment", downloadFileName);
        // application/octet-stream:二进制流数据（最常见的文件下载）
        headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
        ResponseEntity<byte[]> responseEntity = new ResponseEntity<byte[]>(FileUtils.readFileToByteArray(tfile),
                headers, HttpStatus.CREATED);
        return responseEntity;
    }

    /**
     * 综合成绩信息 导出到excel
     * @param request
     * @param response
     * @param model
     * @return
     * @throws IOException
     */
    @RequestMapping(value = "/downloadexcel1")
    public ResponseEntity<byte[]> down1(HttpServletRequest request, HttpServletResponse response, Model model)
            throws IOException {
        // 从数据库读取数据
        List<Mark> allMark = studentService.findAllMarks();
        // 获取服务路径
        // String path = “服务路径”;
        //本地路径
        String path = "D:\\excel\\upload";
        File file1=new File(path);
        if(!file1.exists()){
            file1.mkdirs();
        }
        String filename = "ccc.xls";     // 也可修改为 xls
        // 存储File
        File tfile = new File(path + "\\" + filename);
        // 目录
        File mfile = new File(path);
        if (!tfile.exists()) {
            mfile.mkdir();
        }
        // 生成excel
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet("管理员信息表");
        int rownum = 0;
        XSSFRow row1 = sheet.createRow(rownum);
        row1.createCell(0).setCellValue("姓名");
        row1.createCell(1).setCellValue("班级");
        row1.createCell(2).setCellValue("方向");
        row1.createCell(3).setCellValue("阶段");
        row1.createCell(4).setCellValue("阶段成绩");
        row1.createCell(5).setCellValue("周考平均分");
        row1.createCell(6).setCellValue("出勤积分");
        row1.createCell(7).setCellValue("作业积分");
        row1.createCell(8).setCellValue("综合成绩");
        rownum =1;   //这里再次赋值 是为了把标题头加上
        for (Mark mark : allMark) {
            XSSFRow row = sheet.createRow(rownum);
            row.createCell(0).setCellValue(mark.getStudent().getStname());
            row.createCell(1).setCellValue(mark.getStudent().getCid());
            row.createCell(2).setCellValue(mark.getStudent().getClazz().getCname());
            row.createCell(3).setCellValue(mark.getStage().getStname());
            row.createCell(4).setCellValue(mark.getScore6());
            row.createCell(5).setCellValue((mark.getScore1()+mark.getScore2()+mark.getScore3()+mark.getScore4()+mark.getScore5())/5);
            row.createCell(6).setCellValue(mark.getAttendance());
            row.createCell(7).setCellValue(mark.getHomeworks());
            row.createCell(8).setCellValue(mark.getScore6()*0.5+0.5*(mark.getScore1()+mark.getScore2()+mark.getScore3()+mark.getScore4()+mark.getScore5())/5+mark.getAttendance()+mark.getHomeworks());
            rownum++;
        }
        // 保存workbook
        try {
            workbook.write(new FileOutputStream(tfile));
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        // 创建请求头对象
        HttpHeaders headers = new HttpHeaders();
        // 下载显示的文件名，解决中文名称乱码问题
        String downloadFileName = new String(filename.getBytes("UTF-8"), "iso-8859-1");
        // 通知浏览器以attachment(下载方式)打开
        headers.setContentDispositionFormData("attachment", downloadFileName);
        // application/octet-stream:二进制流数据（最常见的文件下载）
        headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
        ResponseEntity<byte[]> responseEntity = new ResponseEntity<byte[]>(FileUtils.readFileToByteArray(tfile),
                headers, HttpStatus.CREATED);
        return responseEntity;
    }

    /**
     * 阶段考成绩信息 导出到excel
     * @param request
     * @param response
     * @param model
     * @return
     * @throws IOException
     */
    @RequestMapping(value = "/downloadexcel2")
    public ResponseEntity<byte[]> down2(HttpServletRequest request, HttpServletResponse response, Model model)
            throws IOException {
        // 从数据库读取数据
        List<Mark> allMark = studentService.findAllMarks();
        // 获取服务路径
        // String path = “服务路径”;
        //本地路径
        String path = "D:\\excel\\upload";
        File file1=new File(path);
        if(!file1.exists()){
            file1.mkdirs();
        }
        String filename = "ddd.xls";     // 也可修改为 xls
        // 存储File
        File tfile = new File(path + "\\" + filename);
        // 目录
        File mfile = new File(path);
        if (!tfile.exists()) {
            mfile.mkdir();
        }
        // 生成excel
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet("管理员信息表");
        int rownum = 0;
        XSSFRow row1 = sheet.createRow(rownum);
        row1.createCell(0).setCellValue("姓名");
        row1.createCell(1).setCellValue("学号");
        row1.createCell(2).setCellValue("班级");
        row1.createCell(3).setCellValue("阶段考成绩");
        rownum =1;   //这里再次赋值 是为了把标题头加上
        for (Mark mark : allMark) {
            XSSFRow row = sheet.createRow(rownum);
            row.createCell(0).setCellValue(mark.getStudent().getStname());
            row.createCell(1).setCellValue(mark.getSid());
            row.createCell(2).setCellValue(mark.getStudent().getCid());
            row.createCell(3).setCellValue(mark.getScore6());
            rownum++;
        }
        // 保存workbook
        try {
            workbook.write(new FileOutputStream(tfile));
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        // 创建请求头对象
        HttpHeaders headers = new HttpHeaders();
        // 下载显示的文件名，解决中文名称乱码问题
        String downloadFileName = new String(filename.getBytes("UTF-8"), "iso-8859-1");
        // 通知浏览器以attachment(下载方式)打开
        headers.setContentDispositionFormData("attachment", downloadFileName);
        // application/octet-stream:二进制流数据（最常见的文件下载）
        headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
        ResponseEntity<byte[]> responseEntity = new ResponseEntity<byte[]>(FileUtils.readFileToByteArray(tfile),
                headers, HttpStatus.CREATED);
        return responseEntity;
    }

}
